package spectra.ee.web.memo.service;

import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import com.ibatis.sqlmap.client.SqlMapClient;

import spectra.base.log.Logger;
import spectra.base.sql.ibatis.SqlMapperWeb;
import spectra.base.util.DateUtil;
import spectra.base.util.StringUtil;
import spectra.ee.web.WebPublic;
import spectra.ee.web.memo.model.Memo;
import spectra.ee.web.memo.model.MemoContents;
import spectra.ee.web.kbhelper.model.KbHelper;

/**
 * 메모 관련 클래스.
 *
 * @author kmhan
 */
public class MemoServiceBase
{

    /** Logger객체. */
    private static Logger logger = Logger.getLoggerInstance(MemoServiceBase.class.getName());

    /** SqlMapClient 객체. */
    protected static SqlMapClient sqlMap = SqlMapperWeb.getSqlMapWebInstance();

    /**
     * 지식메모 리스트를 가져오는 메소드이다.
     * @param pageNo
     * @param rowsPerPage
     * @param createdBy
     * @param condition
     * @param searchText
     * @return
     */
    public List selectMyMemoList(int pageNo, int rowsPerPage, String domainId, String createdBy, String condition, String searchText)
    {
        List list = null;
        try
        {
        	String currDate = DateUtil.getCurrDate("yyyyMMddHHmmss");
        	KbHelper obj = new KbHelper();
        	obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
        	obj.setDomainId(domainId);
        	obj.setCreatedBy(createdBy);
        	obj.setCurrentDate(currDate);
        	obj.setStartNo((pageNo-1) * rowsPerPage + 1);
       	 	obj.setEndNo(pageNo * rowsPerPage);
       	 	obj.setCondition(condition);
       	 	obj.setRowsPerPage(rowsPerPage);
    	 	if (searchText != null)
    	 	{
	 	   	 	searchText = searchText.replaceAll("_", "\\\\_"); // 쿼리 escape지정
	 		 	searchText = searchText.replaceAll("%", "\\\\%"); // 쿼리 escape지정
    	 	}
    	 	obj.setSearchText(searchText);
 	 	
            list = (List)sqlMap.queryForList("Memo.selectMemoList", obj);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return list;
    }
    
    /**
     * 지식메모 리스트 개수를 가져오는 메소드이다.
     * @param createdBy
     * @param condition
     * @param searchText
     * @return
     */
    public int selectMyMemoListCount(String domainId, String createdBy, String condition, String searchText)
    {   	 
   	 	int iResult = 0;
   	 	String currDate = DateUtil.getCurrDate("yyyyMMddHHmmss");
   	 	KbHelper obj = new KbHelper();
   	 	obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
   	 	obj.setDomainId(domainId);
   	 	obj.setCreatedBy(createdBy);
   	 	obj.setCurrentDate(currDate);   	 	
	 	obj.setCondition(condition);
	 	if (searchText != null)
	 	{
	   	 	searchText = searchText.replaceAll("_", "\\\\_"); // 쿼리 escape지정
		 	searchText = searchText.replaceAll("%", "\\\\%"); // 쿼리 escape지정
	 	}
	 	obj.setSearchText(searchText);
	 	
        try
        {
        	iResult = ((Integer)sqlMap.queryForObject("Memo.selectMemoListCount", obj)).intValue();
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return iResult;
    } 
    
    /**
     * accountId, kbId로 메모를 가져오는 메소드
     * @param accountId
     * @param kbId
     * @return
     */
    public List selectMemoListByKbId(String createdBy, String kbId)
    {
   	 	List list = null;
        try
        {
        	Memo obj = new Memo();
       	 	obj.setCreatedBy(createdBy);
       	 	obj.setTargetId(kbId);
       	 	list = (List)sqlMap.queryForList("Memo.selectMemoListByKbId", obj);
       	 	if (list != null)
       	 	{
       	 		List memoContentsList = selectMemoContents(createdBy, kbId);
       		 
       	 		for (Iterator it = list.iterator(); it.hasNext();)
       	 		{
       	 			Memo memoObj = (Memo)it.next();
       	 			String memoId = memoObj.getMemoId();
       	 			
       	 			String contents = "";
       	 			for (Iterator itContents = memoContentsList.iterator(); itContents.hasNext();)
       	 			{
       	 				MemoContents memoContentsObj = (MemoContents) itContents.next();
       	 				if (memoId.equals(memoContentsObj.getMemoId()))
       	 				{
       	 					contents += StringUtil.nvl(memoContentsObj.getContents(), "");
       	 				}
       	 			}
       	 			
       	 			memoObj.setContents(contents);
       	 		}
       	 	}
        }
        catch (Exception e)
        {
        	logger.error(e.getMessage());
        }
        return list;
    }
    
    public List selectMemoContents(String createdBy, String kbId)
    {
        List list = null;
        try
        {
       	 	Memo obj = new Memo();
       	 	obj.setCreatedBy(createdBy);
       	 	obj.setTargetId(kbId);
            list = (List)sqlMap.queryForList("Memo.selectMemoContentsByAccountIdKbId", obj);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return list;
    }
    
    /**
     * accountId, kbId로 메모를 입력하는 메소드
     * @param accountId
     * @param kbId
     * @return
     */
    public boolean insertMemo(String createdBy, String kbId, String memoContents)
    {
        boolean result = false;
        try
        {
       	 	String currDate = DateUtil.getCurrDateTimeStamp();
            Memo obj = new Memo();
            obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
            obj.setTargetId(kbId);
            obj.setProcessSeq(-1);
            obj.setDeleteFlag(WebPublic.FLAG_N);
            obj.setCreatedBy(createdBy);
            obj.setCreatedDate(currDate);
            obj.setUpdatedBy(createdBy);
            obj.setUpdatedDate(currDate);
            
            sqlMap.startTransaction();
            
       	 	String memoId = (String) sqlMap.queryForObject("Memo.selectNextKey", null);
       	 	obj.setMemoId(memoId);
       	 	// t_memo테이블에 insert
            sqlMap.insert("Memo.insert", obj);
            
            String[] arMemoContents = StringUtil.splitContents(memoContents, WebPublic.MAX_CONTENTS_LENGTH);
            
            MemoContents memoContentsObj = null;
            for (int i=0; i<arMemoContents.length; i++)
            {
           	 	memoContentsObj = new MemoContents();
	             memoContentsObj.setMemoId(memoId);
	             memoContentsObj.setContentsSeq(i);
	             memoContentsObj.setContents(arMemoContents[i]);
	             memoContentsObj.setCreatedDate(currDate);
	             // t_memo_contents에 insert
	             sqlMap.insert("Memo.insertMemoContents", memoContentsObj);
            }
            
            sqlMap.commitTransaction();
            result = true;
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }   
        finally
        {
            try
            {
                sqlMap.endTransaction();
            }
            catch(SQLException e)
            {
                logger.error(e.getMessage());
            }
        }
        return result;
    }
    
    /**
     * 메모를 수정하는 메소드
     * @param accountId
     * @param kbId
     * @param memoId
     * @param memoContents
     * @return
     */
    public boolean updateMemo(String accountId,  String kbId, String memoId, String memoContents)
    {
        boolean result = false;
        try
        {
       	 	String currDate = DateUtil.getCurrDateTimeStamp();
            Memo obj = new Memo();
            obj.setMemoId(memoId);
            obj.setUpdatedBy(accountId);
            obj.setUpdatedDate(currDate);
            
            sqlMap.startTransaction();
            
            // t_memo테이블에 insert
            sqlMap.update("Memo.updateMemo", obj);
            
            // t_memo_contents 삭제
            sqlMap.delete("Memo.deleteMemoContentsByMemoId", memoId);
            
            String[] arMemoContents = StringUtil.splitContents(memoContents, WebPublic.MAX_CONTENTS_LENGTH);
            
            MemoContents memoContentsObj = null;
            for (int i=0; i<arMemoContents.length; i++)
            {
           	 	memoContentsObj = new MemoContents();
	            memoContentsObj.setMemoId(memoId);
	            memoContentsObj.setContentsSeq(i);
	            memoContentsObj.setContents(arMemoContents[i]);
	            memoContentsObj.setCreatedDate(currDate);
	            sqlMap.insert("Memo.insertMemoContents", memoContentsObj);
            }
            
            sqlMap.commitTransaction();
            
            result = true;
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }         
        finally
        {
            try
            {
                sqlMap.endTransaction();
            }
            catch(SQLException e)
            {
                logger.error(e.getMessage());
            }
        }
        return result;
    }
    
    /**
     * 메모를 삭제하는 메소드
     * @param accountId
     * @param kbId
     * @param memoId
     * @param memoContents
     * @return
     */
    public boolean deleteMemo(String accountId,  String kbId, String memoId)
    {
        boolean result = false;
        try
        {
       	 	String currDate = DateUtil.getCurrDateTimeStamp();
            Memo obj = new Memo();
            obj.setMemoId(memoId);
            obj.setDeleteFlag(WebPublic.FLAG_Y);
            obj.setUpdatedBy(accountId);
            obj.setUpdatedDate(currDate);
            
       	 	// t_memo테이블에 update
            sqlMap.update("Memo.updateMemo", obj);
                         
            result = true;
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return result;
    }

    
}
